package com.ids.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.ids.bean.AttendanceType;
import com.ids.bean.TeamAttendanceData;
import com.ids.bean.UserInfo;
import com.ids.util.DbManager;
import com.mysql.jdbc.Statement;

public class TeamAttendanceDao {

	private static TeamAttendanceDao instance = new TeamAttendanceDao();

	private TeamAttendanceDao() {
	}

	public static TeamAttendanceDao getInstance() {
		return instance;
	}

	public List<TeamAttendanceData> getTeamAttendanceData(UserInfo userInfo) {
		List<TeamAttendanceData> teamAttendanceData = null;
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		String empCode = userInfo.getEmpCode();
		int approved = 1;
		ResultSet teamAttendanceDataRS = null;
		ResultSet attendanceTypeRS = null;
		ResultSet statusTypeRS = null;
		String teamAttendanceDataSql = "select firstName,lastName,a.attendanceTypeId ,a.dependantAttendanceTypeId, statusId , a.dated ,a.empCode "
				+ "from attendance a , employees e   "
				+ "where e.reportingTo =? and a.statusId =? and a.empcode = e.empcode order by a.dated";
		String attendanceTypeSql = "select * from attendancetype";
		String statusTypeSql = "select * from status";
		Statement stateAttendanceType = null;
		Statement stateStatusType = null;
		List<AttendanceType> attendanceType = null;
		List<AttendanceType> statusType = null;
		try {
			stateAttendanceType = (Statement) conn.createStatement();
			attendanceTypeRS = stateAttendanceType.executeQuery(attendanceTypeSql);
			attendanceType = new ArrayList<AttendanceType>();
			AttendanceType attendanceTypeBeans = null;
			while (attendanceTypeRS.next()) {
				attendanceTypeBeans = new AttendanceType();
				attendanceTypeBeans.setId(attendanceTypeRS.getInt("attendanceTypeId"));
				attendanceTypeBeans.setValue(attendanceTypeRS.getString("attendanceType"));
				attendanceType.add(attendanceTypeBeans);
			}
			try {
				stateStatusType = (Statement) conn.createStatement();
				statusTypeRS = stateStatusType.executeQuery(statusTypeSql);
				statusType = new ArrayList<AttendanceType>();
				AttendanceType statusTypeBeans = null;
				while (statusTypeRS.next()) {
					statusTypeBeans = new AttendanceType();
					statusTypeBeans.setId(statusTypeRS.getInt("statusId"));
					statusTypeBeans.setValue(statusTypeRS.getString("statusValue"));
					statusType.add(statusTypeBeans);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

			try {
				PreparedStatement preStmt = conn
						.prepareStatement(teamAttendanceDataSql);
				preStmt.setString(1, empCode);
				preStmt.setInt(2, approved);
				teamAttendanceDataRS = preStmt.executeQuery();
				teamAttendanceData = new ArrayList<TeamAttendanceData>();
				TeamAttendanceData teamAttendanceDataBean = null;

				while (null != teamAttendanceDataRS && teamAttendanceDataRS.next()) {
					teamAttendanceDataBean = new TeamAttendanceData();
					teamAttendanceDataBean.setFullName(teamAttendanceDataRS
							.getString("firstName")
							+ " " + teamAttendanceDataRS.getString("lastName"));
					teamAttendanceDataBean.setDated(new Date(
							teamAttendanceDataRS.getDate("dated").getTime()));
					teamAttendanceDataBean
							.setAttendanceTypeId(teamAttendanceDataRS
									.getInt("attendanceTypeId"));
					teamAttendanceDataBean
							.setDependantAttendanceTypeId(teamAttendanceDataRS
									.getInt("dependantAttendanceTypeId"));
					teamAttendanceDataBean.setEmpCode(teamAttendanceDataRS
							.getString("empCode"));
					teamAttendanceDataBean
							.setAttendanceTypeData(attendanceType);
					teamAttendanceDataBean.setStatusTypeData(statusType);
					teamAttendanceDataBean
							.setDependantAttendanceTypeData(attendanceType);
					teamAttendanceData.add(teamAttendanceDataBean);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbManager.closeResultSet(teamAttendanceDataRS);
			DbManager.closeConnection(conn);
		}
		return teamAttendanceData;
	}

}
